1创建数据库
CREATE DATABASE IF NOT EXISTS school
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
2查看和选择数据库
SHOW CREATE DATABASE school;
USE school;
3创建学生表
CREATE TABLE student (
sno CHAR(10) NOT NULL PRIMARY KEY COMMENT '学号',
sname VARCHAR(30) NOT NULL COMMENT '姓名',
sex ENUM('男','女') NOT NULL DEFAULT '男' COMMENT '性别',
age INT NOT NULL COMMENT '年龄',
major VARCHAR(50) NOT NULL COMMENT '专业',
dept VARCHAR(50) NOT NULL COMMENT '院系'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
4创建课程表（带 CHECK）
CREATE TABLE course (
cno CHAR(10) NOT NULL PRIMARY KEY COMMENT '课程号',
cname VARCHAR(50) NOT NULL COMMENT '课程名',
credit DECIMAL(3,1),
CONSTRAINT chk_credit CHECK(credit >= 0.5 AND credit <= 8.0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
5创建选课表（外键+联合主键）
CREATE TABLE sc (
sno CHAR(10) NOT NULL,
cno CHAR(10) NOT NULL,
score DECIMAL(5,2),
PRIMARY KEY (sno, cno),
CONSTRAINT fk_sc_sno FOREIGN KEY (sno) REFERENCES student(sno),
CONSTRAINT fk_sc_cno FOREIGN KEY (cno) REFERENCES course(cno),
CONSTRAINT chk_score CHECK(score >= 0 AND score <= 100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
6插入数据
-- ① 插入学生
INSERT INTO student(sno, sname, sex, age, major, dept) VALUES
('s1', '张三', '男', 20, '计算机', '信息学院'),
('s2', '李四', '女', 21, '软件工程', '信息学院'),
('s3', '王五', '男', 19, '数据科学', '信息学院');
-- ② 插入课程
INSERT INTO course(cno, cname, credit) VALUES
('c1', '数据库原理', 3.5),
('c2', '数据结构', 4.0);
-- ③ 插入选课（必须在 student 和 course 之后）
INSERT INTO sc(sno, cno, score) VALUES
('s1', 'c1', 85),
('s1', 'c2', 92),
('s2', 'c1', 78);
7查看表结构
SHOW TABLES;
DESC student;
SHOW CREATE TABLE sc;
8UPDATE 和 DELETE
-- ① 修改张三的专业
UPDATE student SET major = '人工智能' WHERE sname = '张三';
-- ② 所有学生年龄 +1
UPDATE student SET age = age + 1;
-- ③ 删除 s2 在 c1 的选课
DELETE FROM sc WHERE sno = 's2' AND cno = 'c1';
9ALTER TABLE 六连击
-- ① 添加 phone 在 sname 后面
ALTER TABLE student ADD phone VARCHAR(20) AFTER sname;
-- ② 改字段名 major → maj
ALTER TABLE student CHANGE major maj VARCHAR(50);
-- ③ 改 phone 类型为 CHAR(11)
ALTER TABLE student MODIFY phone CHAR(11);
-- ④ 验证修改
DESC student;
-- ⑤ 删除 phone
ALTER TABLE student DROP phone;
-- ⑥ 改回 maj → major
ALTER TABLE student CHANGE maj major VARCHAR(50);
10约束的查看与删除
-- ① 查看 sc 表建表语句，找到 CHECK 约束名
SHOW CREATE TABLE sc;
-- 在输出中找到类似：CONSTRAINT `chk_score` CHECK (...)
-- ② 删除 CHECK 约束（用查到的名字）
ALTER TABLE sc DROP CONSTRAINT chk_score;
-- ③ 重新添加新的 CHECK 约束
ALTER TABLE sc ADD CONSTRAINT chk_score_new
CHECK(score >= 0 AND score <= 150);
11INSERT 的多种姿势
-- ① 部分插入（score 自动为 NULL）
INSERT INTO sc(sno, cno) VALUES('s3', 'c1');
-- ② 查看结果
SELECT * FROM sc;
-- 会看到 s3, c1, NULL
-- ③ 用 REPLACE 更新成绩
REPLACE INTO sc(sno, cno, score) VALUES('s3', 'c1', 88);
-- REPLACE 发现主键(s3,c1)已存在 → 先删旧记录 → 再插新记录
12外键的"拦路虎"效果
-- ① 尝试插入不存在的学号（❌ 会报错 ERROR 1452）
INSERT INTO sc(sno, cno, score) VALUES('s99', 'c1', 60);
-- ② 尝试直接删除张三（❌ 会报错 ERROR 1451）
DELETE FROM student WHERE sno = 's1';
-- ③ 正确的删除顺序
DELETE FROM sc WHERE sno = 's1'; -- 先删从表中引用 s1 的记录
DELETE FROM student WHERE sno = 's1'; -- 再删主表中的 s1 ✅
13修改数据库参数
-- ① 改为 utf8
ALTER DATABASE school
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
-- ② 查看效果
SHOW CREATE DATABASE school;
-- ③ 改回 utf8mb4
ALTER DATABASE school
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
14安全删表的正确顺序
-- sc 通过外键引用了 student 和 course
-- 所以必须先删 sc（从表），再删 student 和 course（主表）
DROP TABLE IF EXISTS sc; -- 第一个删：从表
DROP TABLE IF EXISTS student; -- 第二个删：主表
DROP TABLE IF EXISTS course; -- 第三个删：主表
-- student 和 course 之间无外键关系，谁先删都行
15🏫 从零搭建图书馆系统
-- Step 1: 创建数据库
CREATE DATABASE IF NOT EXISTS library
DEFAULT CHARACTER SET utf8mb4;
-- Step 2: 切换数据库
USE library;
-- Step 3: 创建读者表
CREATE TABLE reader (
reader_id CHAR(6) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
phone VARCHAR(20)
) ENGINE=InnoDB;
-- Step 4: 创建图书表
CREATE TABLE book (
book_id CHAR(10) PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(50),
price DECIMAL(6,2)
) ENGINE=InnoDB;
-- Step 5: 创建借阅表
CREATE TABLE borrow (
reader_id CHAR(6) NOT NULL,
book_id CHAR(10) NOT NULL,
borrow_date DATE NOT NULL,
PRIMARY KEY (reader_id, book_id),
FOREIGN KEY (reader_id) REFERENCES reader(reader_id),
FOREIGN KEY (book_id) REFERENCES book(book_id)
) ENGINE=InnoDB;
-- Step 6: 插入数据
INSERT INTO reader VALUES
('R001', '小明', '13800001111'),
('R002', '小红', '13900002222');
INSERT INTO book VALUES
('B001', '数据库原理', '王珊', 49.00),
('B002', 'Python编程', '张三', 39.80),
('B003', '机器学习', '周志华', 89.00);
INSERT INTO borrow VALUES
('R001', 'B001', '2025-03-15'),
('R002', 'B003', '2025-03-18');
-- Step 7: 查看所有表
SHOW TABLES;
-- Step 8: 修改价格
UPDATE book SET price = 59.90 WHERE book_id = 'B002';
-- Step 9: 删除借阅
DELETE FROM borrow WHERE reader_id = 'R002' AND book_id = 'B003';
16🔧 表结构大改造
-- ① 添加 publisher 在 author 后面
ALTER TABLE book ADD publisher VARCHAR(50) AFTER author;
-- ② 改字段名 author → writer
ALTER TABLE book CHANGE author writer VARCHAR(50);
-- ③ 改 price 精度
ALTER TABLE book MODIFY price DECIMAL(8,2);
-- ④ 验证修改
SHOW CREATE TABLE book;
-- ⑤ 删除 publisher
ALTER TABLE book DROP publisher;
17💾 数据的增删改全流程
-- ① 新增学生
INSERT INTO student VALUES
('s4', '赵六', '女', 20, '人工智能', '信息学院'),
('s5', '钱七', '男', 22, '网络安全', '信息学院');
-- ② 新增课程
INSERT INTO course VALUES('c3', 'Python编程', 3.0);
-- ③ s4/s5 选 c3，成绩未知
INSERT INTO sc(sno, cno) VALUES
('s4', 'c3'),
('s5', 'c3');
-- ④ 录入 s4 的 c3 成绩
UPDATE sc SET score = 95 WHERE sno = 's4' AND cno = 'c3';
-- ⑤ 所有课程学分 +0.5
UPDATE course SET credit = credit + 0.5;
-- ⑥ s5 退选 c3
DELETE FROM sc WHERE sno = 's5' AND cno = 'c3';
18🧹 清理大扫除
-- ① 先删从表 borrow（引用了 reader 和 book）
DROP TABLE IF EXISTS borrow;
-- 再删主表 book 和 reader（顺序随意）
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS reader;
-- ② 删除 library 数据库
DROP DATABASE IF EXISTS library;
-- ③ 确认
SHOW DATABASES;
19🐛 Bug Hunter：5个错误
5 处错误：
❶ CREAT → CREATE（少了一个 E）
❷ eid INT PRIMARY KEY 后面缺逗号 → 加上 ,
❸ REFERENCE → REFERENCES（少了一个 S）
❹ 最后一行 utf8mb4 后面缺分号 → 加上 ;
❺ department 表可能不存在（这属于逻辑错误，必须先有 department 表才能建外键）
CREATE TABLE employee (
eid INT PRIMARY KEY, -- 修正②：加逗号
ename VARCHAR(50) NOT NULL,
salary DECIMAL(8,2) CHECK(salary > 0),
dept_id CHAR(10),
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 修正④：加分号
20🎯 命令连连看
-- ① 查数据库字符集
SHOW CREATE DATABASE school;
-- 或者：SHOW VARIABLES LIKE 'character%';
-- ② 加字段 email
ALTER TABLE student ADD email VARCHAR(60);
-- ③ s2 转专业
UPDATE student SET major = '网络安全' WHERE sno = 's2';
-- ④ 删 c2（先删 sc 中选了 c2 的记录，再删课程）
DELETE FROM sc WHERE cno = 'c2';
DELETE FROM course WHERE cno = 'c2';
-- ⑤ 查看建表语句
SHOW CREATE TABLE student;
-- ⑥ 查引擎（同样用 SHOW CREATE TABLE）
SHOW CREATE TABLE student;
-- 输出最后一行能看到 ENGINE=InnoDB